import pymysql
def create_table():
    db = pymysql.connect(host='49.233.39.160', user='user', db='lebo16', port=3306,
                         passwd='leboAa!#$123', charset='utf8')
    cursor = db.cursor()
    cursor.execute('drop table if exists students1;')
    sql = """create table students_li (
  studentNo int auto_increment primary key,
  name varchar(10),
  sex varchar(1),
  hometown varchar(20),
  age tinyint(4),
  class varchar(10),
  card varchar(20)
) 
"""
    try:
        cursor.execute(sql)
        print("执行成功")
    except Exception as e:
        print("执行失败，原因是：%s" % e)
    finally:
        db.cursor()
        db.close()

def insert():
    db = pymysql.connect(host='49.233.39.160', user='user', db='lebo16', port=3306,
                         passwd='leboAa!#$123', charset='utf8')
    cur = db.cursor()
    sql1 = """insert into students_li values
('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'),
('002', '诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
('003', '张飞', '男', '南京', '24', '3班', '340322199003247654'),
('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'),
('005', '大乔', '女', '天津', '19', '3班', '340322199004247654'),
('006', '孙尚香', '女', '河北', '18', '1班', '340322199006247654'),
('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),
('008', '小乔', '女', '河南', '15', '3班', null),
('009', '百里守约', '男', '湖南', '21', '1班', ''),
('010', '妲己', '女', '广东', '26', '2班', '340322199607247654'),
('011', '李白', '男', '北京', '30', '4班', '340322199005267754'),
('012', '孙膑', '男', '新疆', '26', '3班', '340322199000297655')"""
    try:
        cur.execute(sql1)
        #   数据提交
        db.commit()
        print('数据表创建成功')
    except Exception as e:
        print('数据库创建失败的原因：%s' % e)
        db.rollback()
    finally:
        #   操作后进行关闭，关闭游标和数据库
        db.cursor()
        db.close()

def search():
    db = pymysql.connect(host='49.233.39.160', user='user', db='lebo16', port=3306,
                         passwd='leboAa!#$123', charset='utf8')

    cur_search = db.cursor()
    sql = """select name as 姓名, age as 年龄 from students_li,
    select distinct name from students_li,
    
    """

    try:
        cur_search.execute(sql, sql2)
        #   数据提交
        db.commit()
        print('查询成功')
    except Exception as e:
        print('查询失败的原因：%s' % e)
        db.rollback()
    finally:
        #   操作后进行关闭，关闭游标和数据库
        db.cursor()
        db.close()

# create_table()
# insert()
search()